﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;


namespace capaDatos
{
    public class SQLSyntaxisGeneration
    {
        public static string stringConexion()
        {
            return ConfigurationManager.ConnectionStrings["GestorDeFlotasDesktop.Properties.Settings.ConnectionString"].ConnectionString;
        }
        public SqlConnection connection = new SqlConnection(stringConexion());

        public DataTable executeQuery(string query)
        {
            SqlCommand command = new SqlCommand(query, connection);
            DataSet ds = new DataSet();
            SqlDataAdapter adaptador = new SqlDataAdapter(command);
            adaptador.Fill(ds);
            return ds.Tables[0];//ver que tabla devolver
        }

        /// <summary>
        /// Metodo utilizado para ejecutar stored procedure de la base de datos 
        /// </summary>
        /// <param name="spName">  nombre del stored procedure</param>
        /// <param name="parametros"> coleccion de parametros </param>
        /// <returns> devuelve un datable con los datos del resultado del sp</returns>
        /// 
        public DataTable executeSp(string spName, ArrayList parametros)
        {

            try
            {
                SqlCommand command = connection.CreateCommand();
                string paramsName = "";

                foreach (SqlParameter sqlParam in parametros)
                {
                    if (sqlParam == parametros[0])
                    {
                        paramsName = paramsName + "@" + sqlParam.ParameterName;
                    }
                    else
                    {
                        paramsName = paramsName + ",@" + sqlParam.ParameterName;
                    }
                    command.Parameters.Add(sqlParam);

                }

                connection.Open();
                command.CommandText = "execute" + (char)32 + spName + (char)32 + paramsName;


                connection.Close();
                SqlDataAdapter adaptador = new SqlDataAdapter(command);

                DataSet ds = new DataSet();

                adaptador.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception e)
            {
             throw e;
            }

        }

    }
}

